package com.audaque.cloud.iqs.dbs.service.impl;


import com.audaque.cloud.iqs.dbs.service.DsHandler;
import com.audaque.cloud.iqs.dbs.vo.ColumnInfo;
import com.audaque.cloud.iqs.dbs.vo.DateVo;
import com.audaque.cloud.iqs.dbs.vo.TableInfo;
import org.apache.commons.collections.CollectionUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * oracle
 * @author HuangHL
 * @date 2022-10-17 16:50:29
 */
public class DsHandlerOracle implements DsHandler {
    @Override
    public boolean canHander(String driveClass) {
        return driveClass.contains("oracle");
    }

    @Override
    public String getTablesSql(String scheme,String user) {
        return "select t.TABLE_NAME,c.COMMENTS AS TABLE_COMMENT from USER_TABLES t left join USER_TAB_COMMENTS c on c.TABLE_NAME=t.TABLE_NAME";
    }

    @Override
    public String getColumnsSql(String scheme,String user,String tableName) {
        return "select T.*,C.COMMENTS,PP.CONSTRAINT_TYPE from USER_TAB_COLUMNS T LEFT JOIN USER_COL_COMMENTS C ON T.COLUMN_NAME=C.COLUMN_NAME AND T.TABLE_NAME=C.TABLE_NAME "
                + "LEFT JOIN (select P.TABLE_NAME,P.CONSTRAINT_TYPE,C1.COLUMN_NAME from USER_CONSTRAINTS P LEFT JOIN USER_CONS_COLUMNS C1 ON C1.OWNER=P.OWNER AND C1.CONSTRAINT_NAME=P.CONSTRAINT_NAME AND C1.TABLE_NAME=P.TABLE_NAME "
                + "           where P.CONSTRAINT_TYPE = 'P' AND P.OWNER='"+scheme+"') PP ON PP.TABLE_NAME=T.TABLE_NAME AND T.COLUMN_NAME=PP.COLUMN_NAME "
                + "WHERE T.TABLE_NAME='"+tableName+"'";
    }

    @Override
    public String getSchema(String schema,String database,String username) {
        return username;
    }

    @Override
    public List<TableInfo> listToTables(List<Map<String, Object>> list) {
        List<TableInfo> res = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(list)) {
            for (Map<String, Object> map:list) {
                String tableName = null;
                String tableDesc = null;
                if(map.get("TABLE_NAME") != null) {
                    tableName = map.get("TABLE_NAME").toString();
                }
                if(map.get("TABLE_COMMENT") != null) {
                    tableDesc = map.get("TABLE_COMMENT").toString();
                }
                TableInfo tableInfo = new TableInfo(tableName,tableDesc);
                res.add(tableInfo);
            }
        }
        return res;
    }

    @Override
    public List<ColumnInfo> listToColumns(List<Map<String, Object>> list, String tableName) {
        List<ColumnInfo> res = new ArrayList<>();
        for (Map<String, Object> map:list) {
            String colName = null;
            String colDesc = null;
            String dataType = null;
            String length = null;
            String po = null;
            String key = "0";
            String isIfNull = "0";
            if(map.get("COLUMN_NAME") != null) {
                colName = map.get("COLUMN_NAME").toString();
            }
            if(map.get("COMMENTS") != null) {
                colDesc = map.get("COMMENTS").toString();
            }
            if(map.get("DATA_LENGTH") != null) {
                length = map.get("DATA_LENGTH").toString();
            }
            if(map.get("DATA_PRECISION") != null) {
                length = map.get("DATA_PRECISION").toString();
            }
            if(map.get("DATA_TYPE") != null) {
                String tmp = map.get("DATA_TYPE").toString();
                if(tmp.contains("(")) {
                    tmp = tmp.substring(0,tmp.indexOf("("));
                    if(map.get("DATA_SCALE") != null) {
                        length = map.get("DATA_SCALE").toString();
                    }
                } else {
                    if(map.get("DATA_SCALE") != null) {
                        po = map.get("DATA_SCALE").toString();
                    }
                }
                dataType = tmp;
            }



            if(map.get("CONSTRAINT_TYPE") != null) {
                String pk = map.get("CONSTRAINT_TYPE").toString();
                if("P".equals(pk)){
                    key = "1";
                }
            }
            if(map.get("NULLABLE") != null) {
                String isNull = map.get("NULLABLE").toString();
                if("Y".equals(isNull)){
                    isIfNull = "1";
                }
                if("N".equals(isNull)){
                    isIfNull = "0";
                }
            }
            ColumnInfo columnInfo = new ColumnInfo();
            columnInfo.setTableName(tableName);
            columnInfo.setColName(colName);
            columnInfo.setColDesc(colDesc);
            columnInfo.setColType(dataType);
            if(length!=null) {
                columnInfo.setColLength(Long.valueOf(length));
            }
            if(po!=null) {
                columnInfo.setColPrecision(Integer.valueOf(po));
            }
            columnInfo.setKeyFlag(key);
            columnInfo.setNullable(isIfNull);
            if(map.get("COLUMN_ID") != null) {
                String ordinalPosition = map.get("COLUMN_ID").toString();
                columnInfo.setColSeq(Integer.valueOf(ordinalPosition));
            }
            res.add(columnInfo);
        }
        return res;
    }

    @Override
    public String getPage(String sql, int pageNo,int pageSize) {
        int start = (pageNo-1)*pageSize;
        int end = (pageNo)*pageSize;
        String mysql = "SELECT *\n" +
                "FROM (\n" +
                "  SELECT pageaaa.*, ROWNUM r\n" +
                "  FROM ("+sql+") pageaaa\n" +
                "  WHERE ROWNUM <= "+end+"\n" +
                ")\n" +
                "WHERE r >= "+start;
        return mysql;
    }

    @Override
    public String getDateWhereSql(DateVo dateVo) {
        return "to_char("+dateVo.getCol()+",'YYYY-MM-DD')"+dateVo.getWhereTypeStr()+"'"+dateVo.getVal()+"'";
    }

    @Override
    public String prosessAs(String sql) {
        String sql1 = sql.replaceAll("`","\"");
        return sql1;
    }

    @Override
    public String sqlSpilit() {
        return "\"";
    }


}
